1、安装: 移除原来的mysql服务:mysqld -remove MySQL mysqld -install 2、初始化: mysqld --initialize 3、启动: net start mysql 4、登陆: mysql -u root -p 5、修改密码: mysqld --shared-memory --skip-grant-tables ; mysql ; flush privileges ; alter user "root"@"localhost" identified by "root"; 6、授权: GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '密码' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON src.* TO 'src'@'%' ; 7、查看参数: show variables like '%lower%' ; 8、创建数据库: 1、 CREATE DATABASE 数据库名; 2、 GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON 数据库名.* TO 数据库名@localhost IDENTIFIED BY '密码'; grant all privileges on *.* to root@'%'; 3、 SET PASSWORD FOR '数据库名'@'localhost' = OLD_PASSWORD('密码'); 4、创建用户: CREATE USER foo@localhost IDENTIFIED BY '123'; update user set user="新用户名" where user="root" ; 5、更新密码: UPDATE user SET password=PASSWORD("新密码") WHERE user='你的用户名' ; set password for root@localhost = password('@root123'); 远程连接修改密码:ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root1234'; 9、删除字段: alter table id_name drop column age,drop column address; 10、删除数据库: drop database xhkdb ; 11、删除数据库下所有表:show create table S10.TPG_GZFSQB ; SELECT concat('DROP TABLE IF EXISTS ', table_name, ';') FROM information_schema.tables WHERE table_schema = 'mydb'; 12、查看版本号:select version(); 查看端口号:show global variables like 'port'; 13、加载文件:source d:/demo.sql 14、添加表字段:alter table table1 add transactor varchar(10) not Null; 15、建表语句: create table tb_emp(id int primary key auto_increment, ###auto_increment只是MySQL特有的 Name varchar(18), sex varchar(2)); 16、删除所有表数据: Select CONCAT( 'drop table ', table_name, ';' ) FROM information_schema.tables Where table_schema='mydatabase' table_name LIKE ' sql_%'; 17、删数据:you are using safe mode解决方法:SET SQL_SAFE_UPDATES = 0 ; 18、查看创建表语句: 19、查看参数:show variables like 'lower_case_table_names'; 20、解除正在死锁的状态有两种方法: 第一种: 1.查询是否锁表 show OPEN TABLES where In_use > 0 ; 2.查询进程(如果您有SUPER权限,您可以看到所有线程。否则,您只能看到您自己的线程) show processlist ; 3.杀死进程id(就是上面命令的id列) kill id
第二种: 1.查看下在锁的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX ; 2.杀死进程id(就是上面命令的trx_mysql_thread_id列) kill 线程ID 21、参数含义: key_buffer_size=8388608 查看命令:show variables like 'key_buffer_size'; set global key_buffer_size=204800; 为了最小化磁盘的 I/O ,MyISAM 存储引擎的表使用键高速缓存来缓存索引,这个键高速缓存的大小则通过 key-buffer-size 参数来设置。 read_buffer_size=131072 查看命令:show variables like 'read_buffer_size'; set global read_buffer_size=8192; max_used_connections=12 max_threads=200 thread_count=13 connection_count=12 22、创建sequence: a、创建--Sequence 管理表 DROP TABLE IF EXISTS sequence; CREATE TABLE sequence ( name VARCHAR(50) NOT NULL, current_value INT NOT NULL, increment INT NOT NULL DEFAULT 1, PRIMARY KEY (name) ) ENGINE=InnoDB; b、创建--取当前值的函数 DROP FUNCTION IF EXISTS currval; DELIMITER $ CREATE FUNCTION currval (seq_name VARCHAR(50)) RETURNS INTEGER LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN DECLARE value INTEGER; SET value = 0; SELECT current_value INTO value FROM sequence WHERE name = seq_name; RETURN value; END $ DELIMITER ; c、创建--取下一个值的函数 DROP FUNCTION IF EXISTS nextval; DELIMITER $ CREATE FUNCTION nextval (seq_name VARCHAR(50)) RETURNS INTEGER LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN UPDATE sequence SET current_value = current_value + increment WHERE name = seq_name; RETURN currval(seq_name); END $ DELIMITER ; d、创建--更新当前值的函数 DROP FUNCTION IF EXISTS setval; DELIMITER $ CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER) RETURNS INTEGER LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN UPDATE sequence SET current_value = value WHERE name = seq_name; RETURN currval(seq_name); END $ DELIMITER ; e、测试函数功能 INSERT INTO sequence VALUES ('TestSeq', 0, 1);----添加一个sequence名称和初始值,以及自增幅度 SELECT SETVAL('TestSeq', 10);---设置指定sequence的初始值 SELECT CURRVAL('TestSeq');--查询指定sequence的当前值 SELECT NEXTVAL('TestSeq');--查询指定sequence的下一个值
|